package top.IT00544.novel.dao;

import net.sf.json.util.JSONUtils;
import top.IT00544.novel.entity.JsonMsg;
import top.IT00544.novel.entity.Novel;
import top.IT00544.utils.FileUtil;
import top.IT00544.utils.SqliteDBTools;
import top.IT00544.utils.StringUtilsMy;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class NovelDao {
    public Connection conn = null;
    public static String SQL_NOVEL = "create table novels(id,name,author,url_local,url_web,size,cover,url_db_name)";
    public static String SQL_NOVEL_DELETE = "delete from novels where id=?";
    public static String SQL_NOVEL_SELECT_1 = "select count(*) from novels";
    public static String SQL_NOVEL_INSERT = "insert into novels(id,name,author,url_local,url_web,size,cover,url_db_name)values(?,?,?,?,?,?,?,?)";
    public static String SQL_NOVEL_SELECT_ONE = "select id,name,author,url_local,url_web,size,cover,url_db_name from novels where id=?";
    public static String SQL_NOVEL_SELECT_ALL = "select id,name,author,url_local,url_web,size,cover,url_db_name from novels";
    public static String SQL_CHAPTER = "create table chapter(id,name,content)";
    public static String SQL_CHAPTER_INSERT = "insert into chapter(id,name,content)values(?,?,?)";
    public static String SQL_CHAPTER_UPDATE = "update chapter set content=? where id=?";
    public static String SQL_CHAPTER_SELECT_1 = "select count(*) from chapter";
    /**小说目录*/
    public static String SQL_CHAPTER_SELECT_2 = "select id,name from chapter";
    /**小说章节内容*/
    public static String SQL_CHAPTER_SELECT_3 = "select id,content from chapter where id=?";

    public static String DB_BASE_PATH;
    public static String DB_NOVEL;
//    public static String DB_NOVEL_N = DB_BASE_PATH + "sqlite_novel_1.db";

//    private static final Log logger = LogFactory.getLog(NovelDao.class);
    /**
     * ·跟据当前系统判断DB_BASE_PATH的值
     * ·检测数据库目录是否存在，不存在则创建
     */
    static{
        //路径判断
        if("Linux".equals(System.getProperty("os.name"))){
            DB_BASE_PATH = "/home/platinum/.cache/novelReader/";
        }else{
            DB_BASE_PATH = "C:\\ProgramFiles\\novelReader\\";
        }
        System.out.println("===>DB_BASE_PATH = "+DB_BASE_PATH);
        DB_NOVEL = DB_BASE_PATH + "sqlite_novel.db";
        //是否创建目录
        File f = new File(DB_BASE_PATH);
        if(!f.exists()){
            f.mkdirs();
        }
    }
    /**
     * 查询小说某章节内容
     * @param nov url_db_name,id
     * @return
     */
    public Novel getChapterContent(Novel nov){
//        logger.info("查询小说某章节内容中");
        Novel novel = new Novel();
        String dbUrl = DB_BASE_PATH + nov.getUrl_db_name();
        try {
            PreparedStatement ps = getConn(dbUrl).prepareStatement(SQL_CHAPTER_SELECT_3);
            ps.setLong(1,nov.getId());
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                novel.setId(rs.getLong("id"));
                novel.setContent(rs.getString("content"));
            }
            ps.close();
            releaseResource();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return novel;
    }
    /**
     * 查询小说所有章节
     * @param nov url_db_name
     * @return
     */
    public ArrayList<Novel>getCatalogs(Novel nov){
//        logger.info("查询小说章节中");
        ArrayList<Novel> catalogs = new ArrayList<>();
        Novel novel;
        String dbUrl = DB_BASE_PATH + nov.getUrl_db_name();
        try {
            PreparedStatement ps = getConn(dbUrl).prepareStatement(SQL_CHAPTER_SELECT_2);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                novel = new Novel();
                novel.setId(rs.getLong("id"));
                novel.setName(rs.getString("name"));
                catalogs.add(novel);
            }
            ps.close();
            releaseResource();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return catalogs;
    }
    /**
     * 创建小说表
     * 1.统计小说表里小说的数量
     * 2.执行报错，则说明没有小说表，开始创建小说表
     */
    public void createTableNovel(){
        //尝试统计小说表里小说的数量
        try {
            PreparedStatement ps = getConn(DB_NOVEL).prepareStatement(SQL_NOVEL_SELECT_1);
            ps.executeQuery();
            ps.close();
            releaseResource();
        } catch (SQLException throwables) {
//            throwables.printStackTrace();
            System.err.println("没有小说表，开始创建小说表中...");
            //执行报错，则说明没有小说表，开始创建小说表
            PreparedStatement ps2 = null;
            try {
                ps2 = getConn(DB_NOVEL).prepareStatement(SQL_NOVEL);
                ps2.executeUpdate();
                ps2.close();
                releaseResource();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
    /**
     * 创建小说章节表
     * 注：这里不要关闭链接、释放资源，在调用处操作
     * @param dbUrl db文件路径
     */
    private void createTableChapter(String dbUrl){
        //尝试查询章节表的章节数
        try {
            PreparedStatement ps = getConn(dbUrl).prepareStatement(SQL_CHAPTER_SELECT_1);
            ps.executeQuery();
//            ps.close();
//            releaseResource();
        } catch (SQLException throwables) {
//            throwables.printStackTrace();
            System.err.println("没有章节表，开始创建章节表中...");
            //报错，说明没有章节表，开始创建章节表
            PreparedStatement ps2 = null;
            try {
                ps2 = getConn(dbUrl).prepareStatement(SQL_CHAPTER);
                ps2.executeUpdate();
//                ps2.close();
//                releaseResource();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 查询全部小说信息
     * @return
     */
    public ArrayList<Novel> novelInfo(){
//        logger.info("查询全部小说信息");
        ArrayList<Novel> novels = new ArrayList<>();
        Novel novel;
        try {
            PreparedStatement ps = getConn(DB_NOVEL).prepareStatement(SQL_NOVEL_SELECT_ALL);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                novel = new Novel();
                novel.setId(rs.getLong("id"));
                novel.setName(rs.getString("name"));
                novel.setAuthor(rs.getString("author"));
                novel.setUrl_local(rs.getString("url_local"));
                novel.setUrl_web(rs.getString("url_web"));
                novel.setSize(rs.getInt("size"));
                novel.setCover(rs.getString("cover"));
                novel.setUrl_db_name(rs.getString("url_db_name"));
                novels.add(novel);
            }
            ps.close();
            releaseResource();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return novels;
    }
    /**
     * 新增小说
     * <br>1.保存小说信息
     * <br>2.导入小说到数据库
     * @param novel
     */
    public void novelInsert(Novel novel){
        try {
//            1.保存小说信息
            System.out.println("保存小说信息中...");
            PreparedStatement psInsert = getConn(DB_NOVEL).prepareStatement(SQL_NOVEL_INSERT);
            psInsert.setLong(1, novel.getId());
            psInsert.setString(2, novel.getName());
            psInsert.setString(3, novel.getAuthor());
            psInsert.setString(4, novel.getUrl_local());
            psInsert.setString(5, novel.getUrl_web());
            psInsert.setInt(6, novel.getSize());
            psInsert.setString(7, novel.getCover());
            psInsert.setString(8, novel.getUrl_db_name());
            psInsert.executeUpdate();
            psInsert.close();
            //因为章节表在另一db文件中，所以此时要释放当前数据库链接
            releaseResource();
            //2.导入小说到数据库
            chapterInit(novel);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    /**
     * 删除小说
     * @param novel id
     */
    public void novelDelete(Novel novel){
//        SQL_NOVEL_DELETE
        try {
//            1.查询小说信息
            Connection conn = getConn(DB_NOVEL);
            PreparedStatement ps = conn.prepareStatement(SQL_NOVEL_SELECT_ONE);
            ps.setLong(1,novel.getId());
            ResultSet rs = ps.executeQuery();
            String novelPath= DB_BASE_PATH;
            while(rs.next()) {
                novelPath += rs.getString("url_db_name");
            }
            ps.close();
//            2.删除小说章节对应的数据库文件
            File f = new File(novelPath);
            if(f.exists()&&f.isFile()){
                f.delete();
            }
//            3.删除小说在小说表里的信息
            ps = conn.prepareStatement(SQL_NOVEL_DELETE);
            ps.setLong(1,novel.getId());
            ps.executeUpdate();
            ps.close();
            releaseResource();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    /**
	 * 将txt按id、 章节名称、章节内容存入章节表
     * <br>导入方式：本地绝对路径导入
     * @param novel url_db_name,url_local,name,size
	 */
	private void chapterInit(Novel novel) {
        System.out.println("导入小说内容中...");
		boolean flag = true;
		String str,s_jin_du = "进度:",s_tmp;
        String db_url = DB_BASE_PATH + novel.getUrl_db_name();//小说db文件绝对路径
		StringBuffer scontent = new StringBuffer();
		try {
		    String charset = FileUtil.getFileCharset(novel.getUrl_local())
                    ,chapterName = "",s_kong="",s_br="<br>";
            File f = new File(novel.getUrl_local());
            createTableChapter(db_url);//判断是否创建章节表
			System.out.println("读取小说中...");
			FileInputStream fis = new FileInputStream(f);
			InputStreamReader isr = new InputStreamReader(fis,charset);
			BufferedReader br = new BufferedReader(isr);
			System.out.println("获取DB连接...");
			PreparedStatement psInsert = getConn(db_url).prepareStatement(SQL_CHAPTER_INSERT);
			PreparedStatement psUpdate = getConn(db_url).prepareStatement(SQL_CHAPTER_UPDATE);
			System.out.println("开始写入...");
//            int chapterId = 1649;
			int chapterId = 1,size0 = 0,sizeAll = 0;
			long nSize= f.length();
            long size20 = (long) Math.floor(nSize/20);
			//第一章之前的一些简介、序 等
			if(chapterId==1) {
				psInsert.setInt(1, 0);
				psInsert.setString(2, novel.getName());
				psInsert.setString(3, "");
				psInsert.executeUpdate();
			}
			while(flag) {
				str = br.readLine();//当前行的内容
				if(null!=str) {
                    size0 += str.length()*3;//读取进度，单位：比特,一个汉字～=3字节
                    sizeAll += str.length()*3;//读取进度，单位：比特,一个汉字～=3字节
//                   每读取到 1/20 的量就输出一下进度
                    if(size0 > size20) {
                        s_tmp = s_kong + (double)sizeAll/novel.getSize();
                        size0 = 0;
                        System.out.println(s_jin_du+s_tmp);
                    }
                    if(StringUtilsMy.isChapter(str)) {
                        psInsert.setInt(1, chapterId);
                        psInsert.setString(2, str);//章节名称
                        psInsert.setString(3, s_kong);//章节内容
                        psInsert.executeUpdate();
                        psUpdate.setString(1, chapterName + scontent.toString());//上一章节内容
                        psUpdate.setInt(2, chapterId - 1);//上一章节ID
                        psUpdate.executeUpdate();
                        chapterId++;
                        scontent.delete(0, scontent.length());//清空StringBuffer
                        chapterName = str+s_br;//将当前匹配到的章节内容保存起来
                    }else{
                        scontent.append(str);//追加到章节内容里
        //				scontent.append("\n");
                        scontent.append(s_br);//网页展示时用于换行
                    }
				}else{
                    psUpdate.setString(1, scontent.toString());
                    psUpdate.setInt(2, chapterId - 1);
                    psUpdate.executeUpdate();
                    System.out.println("txt end\n");
                    flag = false;
                }
			}
			br.close();
			psInsert.close();
            psUpdate.close();
            releaseResource();
			System.out.println("完成");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
    /**
     * 读取文本文件目录
     * @param novel url_local
     * @param readSome true 读取前128KB内容的目录，false 读取全部目录
     * @return data.chapterNum data.chapters
     */
	public JsonMsg totalChapterNum(Novel novel,boolean readSome){
        JsonMsg jsonMsg = new JsonMsg();
        ArrayList<String> chapters = new ArrayList<>();
        boolean flag = true;
        String str,s_jin_du = "进度:",s_tmp,s_tmp2;
        StringBuffer scontent = new StringBuffer();
        int chapterId = 0;
        try {
            String charset = FileUtil.getFileCharset(novel.getUrl_local()),chapterName = "",s_kong = "";
            File f = new File(novel.getUrl_local());
            FileInputStream fis = new FileInputStream(f);
            InputStreamReader isr = new InputStreamReader(fis,charset);
            BufferedReader br = new BufferedReader(isr);
            long nSize = f.length();
            int size0 = 0,sizeAll = 0,KB128=1024*128;
            long size20 = (long) Math.floor(nSize/20);
            //第一章之前的一些简介、序 等
            while(flag) {
                str = br.readLine();//当前行的内容
                if(null!=str) {
                    size0 += str.length()*3;//读取进度，单位：比特,一个汉字～=3字节
                    sizeAll += str.length()*3;//读取进度，单位：比特,一个汉字～=3字节
                    if(readSome && sizeAll > KB128){
                        flag = false;
                    }
//                   每读取到 1/20 的量就输出一下进度
                    if(size0 > size20) {
                        s_tmp = s_kong + (double)sizeAll/nSize;
                        size0 = 0;
                        System.out.println(s_jin_du+s_tmp);
                    }
                    if(StringUtilsMy.isChapter(str)) {
                        chapterId++;
                        chapters.add(str);
                    }
                }else{
                    flag = false;
                }
            }
            System.out.println("完成");
        } catch (Exception e) {
            e.printStackTrace();
        }
        jsonMsg.getData().put("chapterNum",chapterId);
        jsonMsg.getData().put("chapters",chapters);
        return jsonMsg;
    }
    /**
     * 获取db链接
     * @param dbUrl db地址
     * @return conn
     */
    public Connection getConn(String dbUrl) {
        return SqliteDBTools.getConn(dbUrl);
//        try {
//            if(null == conn||conn.isClosed()) {
//                conn = SqliteDBTools.getConn(dbUrl);
//            }
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }
//        return conn;
    }
    /**关闭db链接，释放资源*/
    public void releaseResource(){
//        SqliteDBTools.releaseDB(null, null, conn);
    }
}
